﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ZX.Web.UI.Controls;
using ZX.SYS.Bussiness;
using System.Data;
using ZX.BLL.Extend;
using ZX.EHR.Library;
using ZX.DBModule;
using ZX.BLL;
using System.Web.Security;
using ZX.SYS.Library;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Data.OleDb;
namespace ZX.WebApp.Message
{
    public partial class orglist : BasePage
    {
        public DataTable das;
        public string otname;
        protected void Page_Load(object sender, EventArgs e)
        {
             otname = Request.QueryString["otname"];
        }

        /// <summary>
        /// 查询EXCEL电子表格添加到DATASET
        /// </summary>
        /// <param name="filenameurl">服务器路径</param>
        /// <param name="table">表名</param>
        public DataTable ExecleDs(string filenameurl, string table)
        {
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            DataTable dt = new DataTable();
            try
            {
                //conn.Open();
                //OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
                //odda.Fill(ds, table);

                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等　
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                //string tableName = "";
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    //if (dtSheetName.Rows[k]["TABLE_NAME"].ToString() == sheet + "$")
                    //{
                    //    tableName = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    //    break;
                    //}
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }
                OleDbDataAdapter myCommand = null;
                
                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                string strExcel = "select [姓],[名],[昵称],[生日],[手机号],[邮箱],[公司],[职位] from [" + strTableNames[0] + "]";
                //string strExcel = "select * from[" + tableName + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(dt);
            }
            catch (Exception ex)
            {
                Response.Write("<script>alert('"+ex.Message+"')</script> ");
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
            {
                Response.Write("<script>alert('请您选择Excel文件')</script> ");
                return;//当无文件时,返回
            }
            string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
            if (IsXls != ".xls")
            {
                Response.Write("<script>alert('只可以选择Excel文件')</script>");
                return;//当选择的不是Excel文件时,返回
            }
            
            string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名 DateTime日期函数
            string savePath = Server.MapPath("../upload/") + filename;//Server.MapPath 获得虚拟服务器相对路径
            FileUpload1.SaveAs(savePath);             //SaveAs 将上传的文件内容保存在服务器上
            das = ExecleDs(savePath, filename);           //调用自定义方法
            DataRow[] dr = das.Select();            //定义一个DataRow数组
            int rowsnum = das.Rows.Count;
            if (rowsnum == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
            }
            else
            {
                GridView1.DataSource = das;
                GridView1.DataBind();
                Session["das"] = das;
                Button2.Visible = true;
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            if (Session["das"] == null)
            {
                Response.Write("<script>alert('请判断是否已经成功导入了，请勿重复导入!')</script>");
                return;
            }
            DataTable dt = (DataTable)Session["das"];
            DataRow[] dr = dt.Select();            //定义一个DataRow数组
            int rowsnum = dt.Rows.Count;
            if (rowsnum == 0)
            {
                Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
            }
            else
            {
                ZX.SYS.Library.MQOrgBody mqob = new MQOrgBody();
                ZX.SYS.Library.MQOTB mqotb = new MQOTB();
                string typeid = Request.QueryString["id"];
                if (typeid == string.Empty || typeid == null)
                {
                    return;
                }
                for (int i = 0; i < dr.Length; i++)
                {
                    string ifb=IfExist(dr[i]["手机号"].ToString(), typeid);
                    if (ifb != "")//已存在的号码不做插入
                    {
                        //continue;
                        mqob.obid = new Guid(ifb);
                        mqob.Xing = dr[i]["姓"].ToString();
                        mqob.Ming = dr[i]["名"].ToString();
                        mqob.NiCheng = dr[i]["昵称"].ToString();
                        try
                        {
                            mqob.ShengRi = Convert.ToDateTime(dr[i]["生日"].ToString());
                        }
                        catch (Exception) { }
                        mqob.ShouJi = dr[i]["手机号"].ToString();
                        mqob.email = dr[i]["邮箱"].ToString();
                        mqob.GongSi = dr[i]["公司"].ToString();
                        mqob.ZhiYe = dr[i]["职位"].ToString();
                        mqob.AddUser = User.UserID;
                        mqob.AddTime = DateTime.Now;
                        if (mqob.Xing == string.Empty || mqob.ShouJi == string.Empty || mqob.Ming == string.Empty)
                            break;

                        BaseBussnies bmqob = new BaseBussnies(mqob);
                        bmqob.Update();
                    }
                    else
                    {
                        mqob.obid = Guid.NewGuid();
                        mqob.Xing = dr[i]["姓"].ToString();
                        mqob.Ming = dr[i]["名"].ToString();
                        mqob.NiCheng = dr[i]["昵称"].ToString();
                        try
                        {
                            mqob.ShengRi = Convert.ToDateTime(dr[i]["生日"].ToString());
                        }
                        catch (Exception) { }
                        mqob.ShouJi = dr[i]["手机号"].ToString();
                        mqob.email = dr[i]["邮箱"].ToString();
                        mqob.GongSi = dr[i]["公司"].ToString();
                        mqob.ZhiYe = dr[i]["职位"].ToString();
                        mqob.AddUser = User.UserID;
                        mqob.AddTime = DateTime.Now;
                        if (mqob.Xing == string.Empty || mqob.ShouJi == string.Empty || mqob.Ming == string.Empty)
                            break;

                        BaseBussnies bmqob = new BaseBussnies(mqob);
                        bmqob.Insert();


                        mqotb.otbid = Guid.NewGuid();
                        mqotb.obid = mqob.obid;
                        mqotb.otid = new Guid(typeid);
                        BaseBussnies bmqt = new BaseBussnies(mqotb);
                        bmqt.Insert();
                    }
                }
            }

            Response.Write("<script>alert('导入成功');</script>");
            ClientScript.RegisterStartupScript(ClientScript.GetType(), "myscript", "<script>RefreshData();</script>");
            Session["das"] = null;
        }

        private string IfExist(string phone,string type)
        { 
            DataTable dt=(new MQIn()).GetBodyByShouji(phone);
            if (dt.Rows.Count > 0)
            {
                ZX.SYS.Library.MQOTB mqotb = new MQOTB();
                mqotb.otbid = Guid.NewGuid();
                mqotb.obid = new Guid(dt.Rows[0]["obid"].ToString());
                mqotb.otid = new Guid(type);
                BaseBussnies bmqt = new BaseBussnies(mqotb);
                bmqt.Insert();
                return dt.Rows[0]["obid"].ToString();
            }
            else
                return "";
        
        }
    }


}